import os.path

import akshare as ak
import configparser
from datetime import datetime
import logging
from logging.config import fileConfig
import pandas as pd
from mongo.MongoHelper import MongoHelper
import requests
import re
import json
import traceback
from tools.mail.mail import emailUtils
from tools.datalog.DataLog import DataLog

cfgdir = os.path.split(os.path.realpath(__file__))[0]
paths = cfgdir.split("/")
a = len(paths)-2
separator = '/'
arr = paths[1:a]
b = '/'+separator.join(arr)+'/'
cfg = os.path.join(cfgdir, 'logging_config.ini')
fileConfig(b+"logging_config.ini", disable_existing_loggers=False)
logger = logging.getLogger('simple_example')


class AkshareWeekly(object):
    def __init__(self, cfg):
        self.config = None
        config = configparser.ConfigParser()
        if cfg:
            config.read(cfg)
        else:
            config.read('../../config.cfg')
        self.symbol = config.get('code', 'symbol')
        self.market = config.get('code', 'market').split(',')
        self.name = config.get('code', 'name')
        database = config.get('db', 'database')
        my_url = config.get('db', 'url')
        self.mongoHelper = MongoHelper(url=my_url, database=database)
        logging.info("AkshareWeekly initialized")
        emailUtils(cfg=None)
        # dataLog = DataLog()

    #
    # 个股资金流
    # 接口: stock_individual_fund_flow
    # 目标地址: https://data.eastmoney.com/zjlx/detail.html
    def individual_cash_flow(self, code, market) -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            stock_individual_fund_flow_df = ak.stock_individual_fund_flow(stock=code, market=market)
            content = stock_individual_fund_flow_df.values.tolist()
            local_coll = self.mongoHelper.mongo_db.get_collection('individualCashFlow')
            for ii, v in enumerate(content):
                total = total + 1
                # code + date
                res = local_coll.find_one({"code": code, "flowDate": v[0]})
                if res:
                    i_total += 1
                else:
                    result = {"code": code, "flowDate": v[0], "closingPrice": v[1], "change": v[2], "netIn": v[3],
                              "netInPercent": v[4], "superNetIn": v[5], "superNetInPercent": v[6], "bigNetIn": v[7],
                              "bigNetInPercent": v[8], "mediumNetIn": v[9], "mediumNetInPercent": v[10],
                              "smallNetIn": v[11], "smallNetInPercent": v[12], "createdTime": datetime.now()}
                    i = local_coll.insert_one(result)
                    if i is not None:
                        a_total += 1
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='个股资金流', content=str(err))
        finally:
            print('个股资金流--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='个股资金流', content=f'{total}条记录')
            record = {'dataName': '个股资金流', 'period': None, 'total': total, 'i_total': i_total, 'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': code, 'exchange': market}
            DataLog.add_log(record)

            return total, i_total, a_total, u_total

    # 行业资金流
    # 接口: stock_fund_flow_industry
    # 目标地址: http://data.10jqka.com.cn/funds/hyzjl/# refCountId=data_55f13c2c_254
    # 描述: 同花顺 - 数据中心 - 资金流向 - 行业资金流
    # 限量: 单次获取指定symbol的行业资金流数据
    # symbol	str	symbol="即时"; choice of {“即时”, "3日排行", "5日排行", "10日排行", "20日排行"}

    def industry_cash_flow(self, symbol=None) -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("industryCashFlow")
            result = ak.stock_fund_flow_industry(symbol="3日排行")
            # '序号', '行业', '行业指数', '行业-涨跌幅', '流入资金', '流出资金', '净额', '公司家数', '领涨股',
            #        '领涨股-涨跌幅', '当前价'
            print(datetime.now())
            for num, row in enumerate(result.values.tolist()):

                q_con = {'code': row[1], 'industry': row[1], 'publicDate': datetime.now().strftime('%Y-%m-%d')}

                total = total + 1
                record = None
                if len(row) == 8:
                    record = {'seq': row[0], 'industry': row[1], 'industryIndex': row[3], 'updown': row[4],
                              'cashFlowIn': row[5], 'cashFlowOut': row[6], 'netAmount': row[7], 'companyAmt': row[2],
                              'upEquity': None, 'upUpDownDegree': None, 'currentQuotation': None,
                              "createdTime": datetime.now(), 'source': 'AK', 'cate': symbol, 'updatedTime': None, 'publicDate': datetime.now().strftime('%Y-%m-%d')}

                elif len(row) == 11:
                    record = {'seq': row[0], 'industry': row[1], 'industryIndex': row[2], 'updown': row[3],
                              'cashFlowIn': row[4], 'cashFlowOut': row[5], 'netAmount': row[6], 'companyAmt': row[7],
                              'upEquity': row[8], 'upUpDownDegree': row[9], 'currentQuotation': row[10],
                              "createdTime": datetime.now(), 'source': 'AK', 'cate': symbol, 'updatedTime': None, 'publicDate': datetime.now().strftime('%Y-%m-%d')}

                else:
                    pass
                if_exist = coll.find_one(q_con)
                if not if_exist:
                    i_res = coll.insert_one(record)
                    if i_res is None:
                        print(f"Error inserting record")
                    else:
                        pass
            logging.info("")
        except Exception as err:
            traceback.print_exc()
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='行业资金流', content=str(err))
        finally:
            print('行业资金流--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='行业资金流', content=f'{total}条记录')
            record = {'dataName': '行业资金流', 'period': None, 'total': total, 'i_total': i_total, 'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)

            return total, i_total, a_total, u_total

    # 机构持股情况
    def institute_holding(self, symbol=None) -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("instituteHolding")
            result = ak.stock_institute_hold(symbol=symbol)
            # '证券代码', '证券简称', '机构数', '机构数变化', '持股比例', '持股比例增幅', '占流通股比例', '占流通股比例增幅'], dtype='object')
            for num, row in enumerate(result.values.tolist()):
                total = total + 1
                q_con = {'code': row[1], 'shortName': row[1], 'statDate': symbol}
                if_exist = coll.find_one(q_con)
                if if_exist is None:
                    record = {'code': row[0], 'shortName': row[1], 'amt': row[2], 'change': row[3], 'percent': row[4],
                              'up': row[5], 'percentFree': row[6], 'upFree': row[7], 'createdTime': datetime.now(),
                              'source': 'AK', 'statDate': symbol}
                    i = coll.insert_one(record)
                    print(i.inserted_id)
                    if i is None:
                        logging.info(f"机构{row[1]}持股{row[1]}{symbol}入库失败")
                    else:
                        logging.info(f"机构{row[1]}持股{row[1]}{symbol}入库成功")
                else:
                    logging.info(f"机构{row[1]}持股{row[1]}{symbol}已经存在")
        except Exception as err:
            traceback.print_exc()
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='机构持股情况', content=str(err))
        finally:
            print('机构持股情况--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='机构持股情况', content=f'{total}条记录')
            record = {'dataName': '机构持股情况', 'period': 'Quarterly', 'total': total, 'i_total': i_total, 'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': symbol, 'code': None, 'exchange': None}
            DataLog.add_log(record)

            return total, i_total, a_total, u_total

    def basic_info(self, symbol=None):
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            df = ak.stock_individual_info_em(symbol=symbol)
            coll = self.mongoHelper.mongo_db.get_collection('equityBasic')
            code = df.query("item=='股票代码'").iloc[0, 1]
            if code:
                re = coll.find_one({"code": code})
                if re:
                    logging.info(f'{code}already exists')
                    logging.info(re['industry'])
                    if re['industry'] is None and re['shortName'] is None:
                        # 更新操作：更新单个文档中的字段
                        filter_condition = {'code': code}  # 用于找到需要更新的文档的条件
                        update_operation = {'$set': {"totalShares": df.query("item=='总股本'").iloc[0, 1],
                                                     "tradeableShares": df.query("item=='流通股'").iloc[0, 1],
                                                     "industry": df.query("item=='行业'").iloc[0, 1],
                                                     "marketDate": df.query("item=='上市时间'").iloc[0, 1],
                                                     "shortName": df.query("item=='股票简称'").iloc[0, 1],
                                                     "generalCapital": df.query("item=='总市值'").iloc[0, 1],
                                                     "tradeableCapital": df.query("item=='流通市值'").iloc[
                                                         0, 1]}}  # 更新操作，使用$set来指定新值
                        u = coll.update_one(filter_condition, update_operation)
                        logging.info(u.matched_count, u.modified_count)
                        if u.modified_count and u.modified_count:
                            print("updated")

                else:
                    record = {"code": df.query("item=='股票代码'").iloc[0, 1],
                              "totalShares": df.query("item=='总股本'").iloc[0, 1],
                              "tradeableShares": df.query("item=='流通股'").iloc[0, 1],
                              "industry": df.query("item=='行业'").iloc[0, 1],
                              "marketDate": df.query("item=='上市时间'").iloc[0, 1],
                              "shortName": df.query("item=='股票简称'").iloc[0, 1],
                              "generalCapital": df.query("item=='总市值'").iloc[0, 1],
                              "tradeableCapital": df.query("item=='流通市值'").iloc[0, 1],
                              "createdTime": datetime.now(), 'source': 'AK', 'exchange': ''}
                    i = coll.insert_one(record)
                    if i is None:
                        logging.info(f"{code}基本信息入库失败")
                    else:
                        logging.info(f"{code}基本信息入库成功{i.inserted_id}")
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='基本信息', content=str(err))
        finally:
            print('基本信息--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='回购', content=f'{total}条回购记录')
            record = {'dataName': '基本信息', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}

            DataLog.add_log(record)

    # 融资融卷
    def financing_securities(self, data=None) -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            result = ak.stock_margin_ratio_pa(date=data)
            coll = self.mongoHelper.mongo_db.get_collection("financialLeverage")
            # ['证券代码', '证券简称', '融资比例', '融券比例']
            for num, row in enumerate(result.values.tolist()):
                total = total + 1
                q_con = {'code': row[1], 'statDate': data}
                if_exist = coll.find_one(q_con)
                if not if_exist:
                    record = {'code': row[0], 'shortName': row[1], 'moneyDebt': row[2], 'securityDebt': row[3],
                              'createdTime': datetime.now(), 'source': 'AK', 'statDate': data}
                    i = coll.insert_one(record)
                    if i is None:
                        print(f"Error inserting record")
                    else:
                        print(f"")
                else:
                    print()
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='融资融卷', content=str(err))
        finally:
            print('融资融卷--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='融资融卷', content=f'{total}条记录')
            record = {'dataName': '融资融卷', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)

            return total, i_total, a_total, u_total

    # 主营业务
    def core_biz(self, symbol=None, exchange='SH') -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("coreBusinessUnit")
            code = exchange.upper() + symbol
            result = ak.stock_zygc_em(symbol=code)
            # ['股票代码', '报告日期', '分类类型', '主营构成', '主营收入', '收入比例', '主营成本', '成本比例', '主营利润',
            #        '利润比例', '毛利率']
            for num, row in enumerate(result.values.tolist()):
                # code + informDate 验重
                # date必须转成datetime，否则查询报错
                date_string = row[1].strftime('%Y-%m-%d')
                datetime_obj = datetime.strptime(date_string, '%Y-%m-%d')
                q_con = {'code': row[0], "informDate": datetime_obj, 'category': '' if pd.isna(row[2]) else row[2], 'corebiz': row[3]}
                if_exist = coll.find_one(q_con)
                if if_exist is None:
                    record = {'code': row[0], 'informDate': datetime_obj, 'category': '' if pd.isna(row[2]) else row[2],
                              'corebiz': row[3], 'coreIncome': row[4], 'ratio': row[5], 'coreCost': row[6],
                              'costRatio': row[7], 'coreProfit': row[8], 'createdTime': datetime.now(), 'source': 'AK'}
                    i = coll.insert_one(record)
                    logging.info(i.inserted_id)
                    if i is None:
                        logging.info(f"主营业务{row[0]}{date_string}入库失败")
                    else:
                        logging.info(f"主营业务{row[0]}{date_string}{i.inserted_id}入库成功")
                else:
                    logging.info(f"主营业务{row[0]}{date_string}已经存在")
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='回购报错', content=str(err))
        finally:
            print('核心业务--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '核心业务', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)

            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='回购', content=f'{total}条回购记录')
            return total, i_total, a_total, u_total

    # 大宗交易明日明细
    def block_transaction_details(self, symbol=None, beg_date='', end_date='') -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("blockTransactionDetail")
            result = ak.stock_dzjy_mrmx(symbol, start_date=beg_date, end_date=end_date)
            # print(datetime.now())
            # ['序号', '交易日期', '证券代码', '证券简称', '涨跌幅', '收盘价', '成交价', '折溢率', '成交量', '成交额',
            #        '成交额/流通市值', '买方营业部', '卖方营业部']
            for num, row in enumerate(result.values.tolist()):
                # code + transactionDate 验重
                total = total + 1
                print(row)
                if not pd.isna(row[1]):
                    date_string = row[1].strftime('%Y-%m-%d')
                    datetime_obj = datetime.strptime(date_string, '%Y-%m-%d')
                    q_con = {'code': row[2], 'transactionDate': datetime_obj}
                    if_exist = coll.find_one(q_con)
                    if not if_exist and len(row) == 13:
                        record = {'seq': row[0], 'transactionDate': row[1].strftime('%Y-%m-%d'), 'code': row[2],
                                  'shortName': row[3], 'upDownDegree': row[4], 'closingQuotation': row[5],
                                  'bidQuotation': row[6], 'overflowRation': row[7], 'bidVolume': row[8],
                                  'bidAmount': row[9], 'bidRatio': row[10], 'buyer': row[11], 'seller': row[12],
                                  'createdTime': datetime.now(), 'source': 'AK'}
                        i = coll.insert_one(record)
                        a_total =+ 1
                        if i is None:
                            print(f"Error inserting record")
                        else:
                            print(f"")
                    if if_exist and len(row) == 13:
                        u_total =+ 1
                    else:
                        i_total =+ 1
                else:
                    i_total += 1
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='大宗交易明日明细', content=str(err))
        finally:
            print('大宗交易明日明细--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='大宗交易明日明细', content=f'{total}条记录')
            record = {'dataName': '大宗交易明日明细', 'period': 'daily', 'total': total, 'i_total': i_total, 'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None, 'exchange': None}
            DataLog.add_log(record)
            return total, i_total, a_total, u_total

    # 美联储利率决议报告
    def usa_bank_interest_rate(self) -> tuple:
        total: int = 0
        i_total: int = 0
        a_total: int = 0
        u_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("usaFederalReserveInterest")
            result = ak.macro_bank_usa_interest_rate()
            # ['商品', '日期', '今值', '预测值', '前值']
            for num, row in enumerate(result.values.tolist()):
                total = total + 1
                if not pd.isna(row[1]):
                    q_con = {'effectiveDate': datetime.strptime(row[1].strftime('%Y-%m-%d'), '%Y-%m-%d')}
                    if_exist = coll.find_one(q_con)
                    if not if_exist:
                        record = {'commodity': row[0],
                                  'effectiveDate': datetime.strptime(row[1].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'currentValue': None if pd.isna(row[2]) else row[2], 'forcastValue': None if pd.isna(row[3]) else row[3], 'previousValue': None if pd.isna(row[4]) else row[4],
                                  'createdDate': datetime.now(), 'source': 'AK', 'updatedTime': None}
                        i = coll.insert_one(record)
                        if i is None:
                            print(f"Error inserting record")
                        else:
                            a_total += 1
                    else:
                        print(f"Inserting record")
                        u_total += 1
                else:
                    i_total += 1
        except Exception as err:
            logging.error(str(err))
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='美联储利率决议报告', content=str(err))
        finally:
            print('美联储利率--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '美联储利率', 'period': None, 'total': total, 'i_total': i_total, 'a_total': a_total, 'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now()}
            DataLog.add_log(record)

            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='美联储利率决议报告', content=f'{total}条记录')
            return total, i_total, a_total, u_total

    # 机构调研记录
    def institute_research(self, beg_date=None) -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("instituteResearch")
            result = ak.stock_jgdy_tj_em(date=beg_date)
            # ['序号', '代码', '名称', '最新价', '涨跌幅', '接待机构数量', '接待方式', '接待人员', '接待地点',
            #        '接待日期', '公告日期']
            for num, row in enumerate(result.values.tolist()):
                total = total + 1
                if not pd.isna(row[9]) and not pd.isna(row[10]):
                    q_con = {'code': row[1], 'visitingDate': datetime.strptime(row[9].strftime('%Y-%m-%d'), '%Y-%m-%d')}
                    if_exist = coll.find_one(q_con)
                    if not if_exist:
                        record = {'seq': row[0], 'code': row[1], 'shortName': row[2], 'latestQuotation': row[3], 'upDownDegree': row[4],
                                  'institutes': row[5], 'visitStyle': row[6], 'receipt': row[7], 'spot': row[8],
                                  'visitingDate': datetime.strptime(row[9].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'publicDate': datetime.strptime(row[10].strftime('%Y-%m-%d'), '%Y-%m-%d'), 'source': 'AK',
                                  'createdTime': datetime.now(), 'updatedTime': None}
                        i = coll.insert_one(record)
                        if i is None:
                            print(f"Error inserting record")
                        else:
                            pass
                    else:
                        pass
        except Exception as err:
            logging.error(str(err))
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='机构调研记录', content=str(err))
        finally:
            print('机构调研--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '机构调研', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)

            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='机构调研记录', content=f'{total}条回购记录')
            return total, i_total, a_total, u_total
    # 上市公司质押比例
    def pledge_ratio(self, beg_date=None) -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("sharePledgeRatio")
            result = ak.stock_gpzy_pledge_ratio_em(date=beg_date)
            # "序号",
            #         "-",
            #         "股票代码",
            #         "股票简称",
            #         "交易日期",
            #         "质押比例",
            #         "质押股数",
            #         "质押笔数",
            #         "无限售股质押数",
            #         "限售股质押数",
            #         "质押市值",
            #         "所属行业",
            #         "近一年涨跌幅",
            #         "所属行业代码",
            #         "-",
            for num, row in enumerate(result.values.tolist()):
                total = total + 1
                print(row)
                if not pd.isna(row[3]):
                    date_string = row[3].strftime('%Y-%m-%d')
                    datetime_obj = datetime.strptime(date_string, '%Y-%m-%d')
                    q_con = {'code': row[1], 'pledgeDate': datetime_obj}
                    if_exist = coll.find_one(q_con)
                    if not if_exist:
                        record = {'seq': row[0], 'code': row[1], 'shortName': row[2], 'pledgeDate': datetime_obj,
                                  'ratio': row[4], 'pledgeShare': row[5], 'pledgeTimes': row[6], 'unlimited': row[7],
                                  'limited': row[8], 'marketValue': row[9], 'industry': row[10], 'changeInYear': row[11],
                                  'industryCode': row[12], 'createdTime': datetime.now(), 'source': 'AK'}
                        i = coll.insert_one(record)
                        if i is None:
                            print(f"Error inserting record")
                        else:
                            print(f"")
                    else:
                        print(f"")
        except Exception as err:
            logging.error(str(err))
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='上市公司质押比例', content=str(err))
        finally:
            print('上市公司质押比例--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '上市公司质押比例', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)

            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='上市公司质押比例', content=f'{total}条记录')
            return total, i_total, a_total, u_total

    # 利润信息
    def profit(self, beg_date=None) -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("incomeSheet")
            result = ak.stock_lrb_em(beg_date)
            # ['序号', '股票代码', '股票简称', '净利润', '净利润同比', '营业总收入', '营业总收入同比', '营业总支出-营业支出',
            #        '营业总支出-销售费用', '营业总支出-管理费用', '营业总支出-财务费用', '营业总支出-营业总支出', '营业利润', '利润总额',
            #        '公告日期']
            for num, row in enumerate(result.values.tolist()):
                total = total + 1
                if not pd.isna(row[14]):
                    q_con = {'code': row[1], 'statDate': beg_date, 'publicDate': datetime.strptime(row[14].strftime('%Y-%m-%d'), '%Y-%m-%d')}
                    if_exits = coll.find_one(q_con)
                    if not if_exits:
                        record = {'seq': row[0], 'code': row[1], 'shortName': row[2], 'netProfit': row[3],
                                  'netProfitChange': row[4], 'totalIncome': row[5], 'totalIncomeChange': row[6],
                                  'totalBizCost': row[7], 'totalSaleFee': row[8], 'totalManageFee': row[9],
                                  'totalCapitalCost': row[10], 'totalCost': row[11], 'marginProfit': row[12],
                                  'totalProfit': row[13],
                                  'publicDate': datetime.strptime(row[14].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'createdTime': datetime.now(), 'source': 'AK', 'statDate': beg_date}
                        i = coll.insert_one(record)
                        if i is None:
                            print(f"Error inserting record")
                        else:
                            print(f"---")
                    else:
                        print(f"---")
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='利润信息', content=str(err))
        finally:
            print('利润信息--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '利润信息', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='利润信息', content=f'{total}条记录')
            return total, i_total, a_total, u_total
    # 资产
    def asset_balance(self, beg_date=None)->tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("assetBalanceSheet")
            result = ak.stock_zcfz_em(beg_date)
            # ['序号', '股票代码', '股票简称', '资产-货币资金', '资产-应收账款', '资产-存货', '资产-总资产',
            #        '资产-总资产同比', '负债-应付账款', '负债-预收账款', '负债-总负债', '负债-总负债同比', '资产负债率',
            #        '股东权益合计', '公告日期']
            for num, row in enumerate(result.values.tolist()):
                total = total + 1
                if not pd.isna(row[14]):
                    q_con = {'code': row[1], 'publicDate': datetime.strptime(row[14].strftime('%Y-%m-%d'), '%Y-%m-%d'), 'statDate': beg_date}
                    if_exits = coll.find_one(q_con)
                    if not if_exits:
                        record = {'seq': row[0], 'code': row[1], 'shortName': row[2], 'cashAmt': row[3],
                                  'receivable': row[4], 'stockAmt': row[5], 'totalAsset': row[6],
                                  'totalAssetChange': row[7], 'payable': row[8], 'preReceivable': row[9],
                                  'totalDebt': row[10], 'totalDebtChange': row[11],
                                  'assetLiabilityRatio': row[12], 'ownership': row[13],
                                  'publicDate': datetime.strptime(row[14].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'createdTime': datetime.now(), 'source': 'AK', 'statDate': beg_date}
                        i = coll.insert_one(record)
                        if i is None:
                            print(f"Error inserting record")
                        else:
                            print(f"")
                    else:
                        print(f"")
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='资产', content=str(err))
        finally:
            print('资产负债--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '资产负债', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='资产', content=f'{total}条记录')
            return total, i_total, a_total, u_total
    # 现金流
    def cash_flow(self, beg_date=None) -> tuple:
        total = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("cashFlowSheet")
            result = ak.stock_xjll_em(beg_date)
            # ['序号', '股票代码', '股票简称', '净现金流-净现金流', '净现金流-同比增长', '经营性现金流-现金流量净额',
            #        '经营性现金流-净现金流占比', '投资性现金流-现金流量净额', '投资性现金流-净现金流占比', '融资性现金流-现金流量净额',
            #        '融资性现金流-净现金流占比', '公告日期']

            for num, row in enumerate(result.values.tolist()):
                total = total + 1
                if not pd.isna(row[11]):
                    q_con = {'code': row[1], 'publicDate': datetime.strptime(row[11].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                            'statDate': beg_date}
                    if_exits = coll.find_one(q_con)
                    if if_exits is None:
                        record = {'seq': row[0], 'code': row[1], 'shortName': row[2], 'netCash': row[3],
                                  'netCashChange': row[4], 'netCashFlow': row[5], 'cashFlowRatio': row[6],
                                  'investingCashFlow': row[7], 'investingCashFlowRatio': row[8],
                                  'financingCashFlow': row[9], 'financingCashFlowRatio': row[10],
                                  'publicDate': datetime.strptime(row[11].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'createdTime': datetime.now(),
                                  'source': 'AK', 'statDate': beg_date}
                        i = coll.insert_one(record)
                        if i is None:
                            print(f"Error inserting record")
                        else:
                            print(f"")
                    else:
                        print(f"")
                else:
                    print(f"")
        except Exception as err:
            traceback.print_exc()
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='现金流', content=str(err))
        finally:
            print('现金流--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '现金流', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)

            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='现金流', content=f'{total}条记录')
            return total, i_total, a_total, u_total
    # 股东增减持
    def holder(self, symbol=None)->tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("shareHolderChange")
            result = ak.stock_ggcg_em(symbol=symbol)
            # ['代码', '名称', '最新价', '涨跌幅', '股东名称', '持股变动信息-增减', '持股变动信息-变动数量',
            #        '持股变动信息-占总股本比例', '持股变动信息-占流通股比例', '变动后持股情况-持股总数', '变动后持股情况-占总股本比例',
            #        '变动后持股情况-持流通股数', '变动后持股情况-占流通股比例', '变动开始日', '变动截止日', '公告日']
            for num, row in enumerate(result.values.tolist()):
                total = total + 1
                # print(row)
                if len(row) == 16 and not pd.isna(row[13]) and not pd.isna(row[14]):
                    q_con = {'code': row[0],
                            'changeBeginDate': datetime.strptime(row[13].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                            'changeEndDate': datetime.strptime(row[14].strftime('%Y-%m-%d'), '%Y-%m-%d')}
                    if_exist = coll.find_one(q_con)
                    if not if_exist:
                        record = {'code': row[0], 'fullName': row[1], 'latestQuotation': row[2], 'upDownDegree': row[3],
                                  'holderName': row[4], 'shareIncrease': row[5], 'shareChangeAmt': row[6],
                                  'percentInTotal': row[7], 'percentInFree': row[8], 'totalPostShares': row[9],
                                  'postPercent': row[10], 'freeShares': row[11],
                                  'percentInTotalFree': row[12],
                                  'changeBeginDate': datetime.strptime(row[13].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'changeEndDate': datetime.strptime(row[14].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'publicDate': datetime.strptime(row[15].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'createdTime': datetime.now(), 'source': 'AK'}
                        i = coll.insert_one(record)
                        if i is None:
                            print(f"Error inserting record")
                        else:
                            print(f"")
                    else:
                        print('')
                else:
                    print(f"data is not available")
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='股东增减持', content=str(err))
        finally:
            print('股东增减持--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '股东增减持', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='股东增减持', content=f'{total}条记录')
            return total, i_total, a_total, u_total
    # 概念资金流
    def concept_cash_flow(self, symbol=None)->tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        coll = self.mongoHelper.mongo_db.get_collection("marketConceptCashFlow")
        ak.stock_fund_flow_concept(symbol)
        # record = {'': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '','': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', }
        return total, i_total, a_total, u_total

    # 大单跟踪
    def big_deal_track(self) -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("bigDealHistory")
            result = ak.stock_fund_flow_big_deal()
            # ['成交时间', '股票代码', '股票简称', '成交价格', '成交量', '成交额', '大单性质', '涨跌幅', '涨跌额']
            for num, row in enumerate(result.values.tolist()):
                total = total + 1
                q_con = {'code': row[1], 'bidDate': row[0]}
                if_exist = coll.find_one(q_con)
                if not if_exist:
                    record = {'bidDate': row[0], 'code': row[1], 'shortName': row[2], 'bidQuotation': row[3],
                              'bidVolumes': row[4], 'bidAmt': row[5], 'attribute': row[6], 'degree': row[7],
                              'amt': row[8], 'createdTime': datetime.now(), 'source': 'AK'}
                    i = coll.insert_one(record)
                    a_total =+ 1
                    if i is None:
                        print(f"大单{row[1]}{row[0]}入库失败")
                    else:
                        print(f"大单{row[1]}{row[0]}{i.inserted_id}入库成功")
                else:
                    print(f"大单{row[1]}{row[0]}已经入库")
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='大单跟踪', content=str(err))
        finally:
            print('大单跟踪--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='大单跟踪', content=f'{total}条记录')
            record = {'dataName': '大单跟踪', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)
            return total, i_total, a_total, u_total

    # 大盘资金流
    def market_fund_flow(self)->tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("marketFundFlow")
            result = ak.stock_market_fund_flow()
            # ['日期', '上证-收盘价', '上证-涨跌幅', '深证-收盘价', '深证-涨跌幅', '主力净流入-净额', '主力净流入-净占比',
            #        '超大单净流入-净额', '超大单净流入-净占比', '大单净流入-净额', '大单净流入-净占比', '中单净流入-净额',
            #        '中单净流入-净占比', '小单净流入-净额', '小单净流入-净占比']
            for num, row in enumerate(result.values.tolist()):
                print(type(row[0]))
                total = total + 1
                date_string = row[0].strftime('%Y-%m-%d')
                datetime_obj = datetime.strptime(date_string, '%Y-%m-%d')
                q_con = {'marketDate': datetime_obj}
                if_exist = coll.find_one(q_con)
                if not if_exist:
                    record = {'marketDate': datetime_obj, 'shClosingQuotation': row[1], 'shUpDown': row[2],
                              'szClosingQuotation': row[3], 'szUpDown': row[4], 'mainForceNetInAmount': row[5],
                              'mainForceNetInRatio': row[6], 'superNetIn': row[7], 'superNetInRatio': row[8],
                              'bigNetIn': row[9], 'bigNetInRatio': row[10], 'mediumNetIn': row[11],
                              'mediumNetInRatio': row[12], 'smallNetIn': row[13], 'smallNetInRatio': row[14],
                              'createdTime': datetime.now(), 'source': 'AK', 'updatedTime': None}
                    i = coll.insert_one(record)
                    if i is None:
                        print(f"大盘资金流{row[0]}入库失败")
                    else:
                        print(f"大盘资金流{row[0]}{i.inserted_id}入库成功")
                else:
                    print(f"大盘资金流{row[0]}已经入库")
        except Exception as err:
            traceback.print_exc()
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='大盘资金流', content=str(err))
        finally:
            print('大盘资金流--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='大盘资金流', content=f'{total}条记录')
            record = {'dataName': '大盘资金流', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)
            return total, i_total, a_total, u_total


    # 散户数量
    def num_of_retail_investor(self, symbol = None)->tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("retailInvestor")
            result = ak.stock_zh_a_gdhs(symbol)
            # ['代码', '名称', '最新价', '涨跌幅', '股东户数-本次', '股东户数-上次', '股东户数-增减', '股东户数-增减比例',
            #        '区间涨跌幅', '股东户数统计截止日-本次', '股东户数统计截止日-上次', '户均持股市值', '户均持股数量', '总市值',
            #        '总股本', '公告日期']
            for num, row in enumerate(result.values.tolist()):
                total += 1
                if len(row) == 16 and row[9] != 'nan' and not pd.isna(row[9]) and row[10] != 'nan' and not pd.isna(
                        row[10]):
                    q_con = {'code': row[0], 'holderThis': datetime.strptime(row[9].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                            'holderLast': datetime.strptime(row[10].strftime('%Y-%m-%d'), '%Y-%m-%d')}
                    if_exist = coll.find_one(q_con)
                    if not if_exist:
                        record = {'code': row[0], 'fullName': row[1], 'latestQuotation': row[2], 'upDownDegree': row[3],
                                  'thisHolderAmt': row[4], 'lastHolderAmt': row[5], 'increase': row[6],
                                  'increaseRatio': row[7], 'periodChange': row[8],
                                  'holderThis': datetime.strptime(row[9].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'holderLast': datetime.strptime(row[10].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'marketValuePerHolder': row[11], 'amtPerHolder': row[12], 'totalMarketValue': row[13],
                                  'totalCapital': row[14],
                                  'publicDate': datetime.strptime(row[15].strftime('%Y-%m-%d'), '%Y-%m-%d'),
                                  'source': 'AK', 'createdTime': datetime.now()}
                        i = coll.insert_one(record)
                        if i is None:
                            print(
                                f"散户数量{row[0]}{row[9].strftime('%Y-%m-%d')}到{row[10].strftime('%Y-%m-%d')}入库失败")
                        else:
                            print(
                                f"散户数量{row[0]}{i.inserted_id}{row[9].strftime('%Y-%m-%d')}到{row[10].strftime('%Y-%m-%d')}入库成功")
                    else:
                        print(f"散户数量{row[0]}{row[9].strftime('%Y-%m-%d')}到{row[10].strftime('%Y-%m-%d')}已经入库")

        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='散户数量', content=str(err))
        finally:
            print('散户数量--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='散户数量', content=f'{total}条记录')
            record = {'dataName': '散户数量', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)
            return total, i_total, a_total, u_total

    # 财务指标
    def financial_indicator(self, symbol=None, begdate=None)->tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        coll = self.mongoHelper.mongo_db.get_collection("financialIndicator")
        result = ak.stock_financial_analysis_indicator(symbol, start_year=begdate)
        # ['日期', '摊薄每股收益(元)', '加权每股收益(元)', '每股收益_调整后(元)', '扣除非经常性损益后的每股收益(元)',
        #        '每股净资产_调整前(元)', '每股净资产_调整后(元)', '每股经营性现金流(元)', '每股资本公积金(元)',
        #        '每股未分配利润(元)', '调整后的每股净资产(元)', '总资产利润率(%)', '主营业务利润率(%)', '总资产净利润率(%)',
        #        '成本费用利润率(%)', '营业利润率(%)', '主营业务成本率(%)', '销售净利率(%)', '股本报酬率(%)',
        #        '净资产报酬率(%)', '资产报酬率(%)', '销售毛利率(%)', '三项费用比重', '非主营比重', '主营利润比重',
        #        '股息发放率(%)', '投资收益率(%)', '主营业务利润(元)', '净资产收益率(%)', '加权净资产收益率(%)',
        #        '扣除非经常性损益后的净利润(元)', '主营业务收入增长率(%)', '净利润增长率(%)', '净资产增长率(%)',
        #        '总资产增长率(%)', '应收账款周转率(次)', '应收账款周转天数(天)', '存货周转天数(天)', '存货周转率(次)',
        #        '固定资产周转率(次)', '总资产周转率(次)', '总资产周转天数(天)', '流动资产周转率(次)', '流动资产周转天数(天)',
        #        '股东权益周转率(次)', '流动比率', '速动比率', '现金比率(%)', '利息支付倍数', '长期债务与营运资金比率(%)',
        #        '股东权益比率(%)', '长期负债比率(%)', '股东权益与固定资产比率(%)', '负债与所有者权益比率(%)',
        #        '长期资产与长期资金比率(%)', '资本化比率(%)', '固定资产净值率(%)', '资本固定化比率(%)', '产权比率(%)',
        #        '清算价值比率(%)', '固定资产比重(%)', '资产负债率(%)', '总资产(元)', '经营现金净流量对销售收入比率(%)',
        #        '资产的经营现金流量回报率(%)', '经营现金净流量与净利润的比率(%)', '经营现金净流量对负债比率(%)', '现金流量比率(%)',
        #        '短期股票投资(元)', '短期债券投资(元)', '短期其它经营性投资(元)', '长期股票投资(元)', '长期债券投资(元)',
        #        '长期其它经营性投资(元)', '1年以内应收帐款(元)', '1-2年以内应收帐款(元)', '2-3年以内应收帐款(元)',
        #        '3年以内应收帐款(元)', '1年以内预付货款(元)', '1-2年以内预付货款(元)', '2-3年以内预付货款(元)',
        #        '3年以内预付货款(元)', '1年以内其它应收款(元)', '1-2年以内其它应收款(元)', '2-3年以内其它应收款(元)',
        #        '3年以内其它应收款(元)']
        for num, row in enumerate(result.values.tolist()):

            q_con = {'code': symbol, 'fiscalYear': begdate}
            if_exist = coll.find_one(q_con)
            if not if_exist:
                record = {'publicDate': '', 'dilutedEarningsPerShare': row[1], 'weightedEarningPerShare': row[2], 'adjustedEarningPerShare': row[3], 'excludedEarningPerShare': row[4], 'netAssetPerShare': row[5], 'adjustedNetAssetPerShare': row[6], 'operatingCashFlowPerShare': row[7], 'capitalFundPerShare': row[8], 'unallocatedEarningPerShare': row[9], 'netAssetAdjustedPerShare': row[10], 'profitRatioPerTotalAsset': row[11],
                  'profitRatioPerKeyBiz': row[12], 'netProfitRatioPerTotalAsset': row[13], 'profitRatioPerCostFee': row[14], 'operatingProfitRatio': row[15], 'costRatioPerKeyBiz': row[16], 'saleNetProfitRatio': row[17], 'returnRatioPerCapital': row[18], 'returnRatioPerNetAsset': row[19], 'returnRatioPerAsset': row[20],
                  'saleMarginRatio': row[21], 'feeRatio': row[22], 'nonKeyBizRatio': row[23], 'keyBizProfitRatio':row[24], 'dividendRatio': row[25], 'investingEarningRatio': row[26], 'keyBizProfit': row[27], 'roe': row[28], 'weightedRoe': row[29],
                  'excludeNetProfit': row[30], 'keyIncomeRatio':row[31], 'netProfitRatio':row[32], 'netAssetRatio':row[33], 'totalAssetRatio': row[34], 'receivableTurnover': row[35],
                  'receivableTurnoverDays': row[36],'inventoryTurnover': row[37],'inventoryTurnoverDays':row[38],'fixedAssetTurnover': row[39],'totalAssetTurnover': row[40],'totalAssetTurnoverDays':row[41],'floatingAssetTurnover': row[42],'floatingAssetTurnoverDays': row[43],'shareHolderEquityTurnover': row[44],'liquidityRatio': row[45],'quickRatio': row[46],'cashRatio': row[47],'interestPaidTimes': row[48],'longDebtPerOperatingCash': row[49],'shareHolderEquityRatio': row[50],'longDebtRatio': row[51],'shareHolderEquityPerFixedAsset': row[52],'debtPerShareHolderEquity': row[53],
                  'longAssetPerLongCash': row[54],'capitalizationRatio': row[55],'fixedAssetNetRatio':row[56],'capitalFixedRatio': row[57],'properyRightRatio': row[58],'settleValueRatio': row[59],
                  'fixedAssetRatio': row[60],'debtPerAsset': row[61],'totalAsset': row[62],'operatingCashPerSaleIncome': row[63],'cashFlowFromAssetRatio': row[64],'operatingCashFlowPerNetProfit':row[65],'operatingCashFlowPerDebt': row[66],'cashFlowRatio': row[67],'shortTermEquityInvesting': row[68],'shortTermSecurityInvesting': row[69],'shortTermOtherInvesting': row[70],'longTermEquityInvesting': row[71],'longTermSecurityInvesting': row[72],'longTermOtherInvesting': row[73],'receivableIn1Year': row[74],'receivableBetween12years': row[75],'receivableBetween23years': row[76],'receivableIn3years': row[77],'advancePaymentIn1Year': row[78],'advancePaymentBetween12Year': row[79]
                  ,'advancePaymentBetween23Year': row[80],'advancePaymentIn3Year': row[81],'otherReceivableIn1years': row[82],'otherReceivableBetween12years': row[83],'otherReceivableBetween23years': row[84],'otherReceivableIn3years': row[85],'code': symbol,'fiscalYear':begdate ,'createdTime': datetime.now(),'source':'AK'}
                i = coll.insert_one(record)
                if i is None:
                    print(f"Error inserting record")
                else:
                    print(f"")
            else:
                print(f"Error inserting record")
            return total, i_total, a_total, u_total
    # 港股财务指标
    def hk_financial_indicator(self, symbol=None, begdate=None):
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        coll = self.mongoHelper.mongo_db.get_collection("hkFinancialIndicator")
        print(ak.stock_financial_hk_analysis_indicator_em(symbol, indicator=begdate).columns)
        # record = {'': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '','': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', '': '', }

    # 十大流通股东
    def free_holder(self,code=None,beg_date=None,exchange=None)->tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("freeHolder")
            result = ak.stock_gdfx_free_top_10_em(symbol=exchange+code, date=beg_date)
            # print(result.columns)
            # print(symbol,beg_date)
            # ['名次', '股东名称', '股东性质', '股份类型', '持股数', '占总流通股本持股比例', '增减', '变动比率']
            if len(result.values.tolist())>0:
                for num, row in enumerate(result.values.tolist()):
                    print(datetime.now())
                    q_con = {'code': code, 'statDate': beg_date, 'exchange': exchange,'rank':row[3]}
                    if_exist = coll.find_one(q_con)
                    if not if_exist:
                        record = {'exchange': exchange, 'code': code, 'statDate': beg_date, 'rank': row[3], 'holderName': row[4], 'holderCate': row[5],
                                  'shareType': row[6], 'num': row[7], 'numRatio': row[8], 'change': row[9],
                                  'changeRatio': row[10], 'createdTime': datetime.now(), 'source': 'AK'}
                        i = coll.insert_one(record)
                        if i is None:
                            logging.info(f"Error inserting record {datetime.now()}")
                        else:
                            logging.info(f"{i.inserted_id}--{datetime.now()}")
                    else:
                        pass
            else:
                pass
        except Exception as err:
            traceback.print_exc()
            logging.error(str(err))
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='十大流通股东', content=str(err))
        finally:
            print('流通股东--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '流通股东', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)
            return total, i_total, a_total, u_total

    # 基金持股
    def fund_holder(self, symbol=None, beg_date=None) -> tuple:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("fundHolding")
            result = ak.stock_report_fund_hold(symbol, date=beg_date)
            if result is not None:
                # print(result.columns)
                print(datetime.now())
                for num, row in enumerate(result.values.tolist()):
                    if row[1] is not None and row[3] is not None and row[4] is not None and row[5] is not None:
                        q_con = {'code': row[1], 'cate': symbol}
                        doc = coll.find_one(q_con)
                        record = {'code': row[1], 'shortName': row[2], 'num': row[3], 'amount': row[4], 'value': row[5],
                                  'change': row[6], 'changeAmt': row[7], 'changePercent': row[8], 'cate': symbol,
                                  'source': 'AK', 'createdTime': datetime.now(), 'updatedTime': None}

                        if doc is not None:
                            record['updateTime'] = datetime.now()
                            u_res = coll.update_one({'_id': doc['_id']}, {'$set': doc })
                            logging.info(f'{u_res.modified_count}--{datetime.now()}')
                        else:
                            i_res = coll.insert_one(record)
                            logging.info(f'{i_res.inserted_id} --{datetime.now()}')
                        total = total + 1
                    else:
                        logging.info('')
            else:
                logging.info('')
        except Exception as err:
            traceback.print_exc()
            logging.error(str(err))
        finally:
            print('基金持股--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '基金持股', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)
            return total, i_total, a_total, u_total

    # 股票回购
    def equity_repurchase(self) -> pd.DataFrame:
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            coll = self.mongoHelper.mongo_db.get_collection("shareRepurchase")
            result = ak.stock_repurchase_em()
            # ['序号', '股票代码', '股票简称', '最新价', '计划回购价格区间', '计划回购数量区间-下限', '计划回购数量区间-上限',
            #        '占公告前一日总股本比例-下限', '占公告前一日总股本比例-上限', '计划回购金额区间-下限', '计划回购金额区间-上限',
            #        '回购起始时间', '实施进度', '已回购股份价格区间-下限', '已回购股份价格区间-上限', '已回购股份数量', '已回购金额',
            #        '最新公告日期']
            for num, row in enumerate(result.values.tolist()):
                # print(row)
                total = total + 1
                date_string = row[17].strftime('%Y-%m-%d')
                datetime_obj = datetime.strptime(date_string, '%Y-%m-%d')
                q_con = {'code': row[1], 'latestPublicDate': datetime_obj}
                if_exist = coll.find_one(q_con)
                if if_exist is None:
                    record = {'seq': row[0], 'code': row[1], 'shortName': row[2], 'latestQuotation': row[3],
                              'quotationRange': row[4], 'upRepurchaseAmt': row[5], 'downRepurchaseAmt': row[6],
                              'downRatio': row[7], 'upRatio': row[8], 'plannedAmtDown': row[9], 'plannedAmtUp': row[10],
                              'repurchasePeriod': row[11].strftime('%Y-%m-%d'), 'progress': row[12],
                              'alreadyDownQuotation': row[13], 'alreadyUpQuotation': row[14],
                              'alreadySharesAmt': row[15], 'alreadyMarketValue': row[16],
                              'latestPublicDate': datetime_obj, 'createdTime': datetime.now(), 'source': 'AK', }
                    i = coll.insert_one(record)
                    if i is None:
                        print(f"{row[0]}{row[11].strftime('%Y-%m-%d')}入库失败")
                        f_total += 1
                    else:
                        a_total += 1
                        print(f"{row[0]}{i.inserted_id}{row[11].strftime('%Y-%m-%d')}入库成功")
                else:
                    i_total += 1
            return result
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'],title='回购报错',content=str(err))
        finally:
            print('股票回购--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'],title='股票回购',content=f'{total}条回购记录')
            record = {'dataName': '股票回购', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)

    #
    def a_hist(self, symbol=None, period='daily', start_date=None, end_date=None, adjust=None) -> tuple:
        total: int = 0
        u_total: int = 0  # 更新数量
        i_total: int = 0  # 数据问题
        a_total: int = 0  # 入库成功
        f_total: int = 0  # 入库失败
        try:
            data = ak.stock_zh_a_hist(
                symbol=symbol,
                period=period,
                start_date=start_date,
                end_date=end_date,
                adjust=adjust
            )
            print(data.columns)
            vs = data.values.tolist()
            coll = self.mongoHelper.mongo_db.get_collection('equityHistory')
            for i, v in enumerate(vs):
                print(v)
                total += 1
                if not pd.isna(v[0]):
                    res = coll.find_one({"code": symbol, 'bizDate': v[0].strftime('%Y-%m-%d'), 'rehabilitation': adjust})
                    if not res:
                        record = {'code': symbol, 'bizDate': v[0].strftime('%Y-%m-%d'), 'openingQuotation': v[1],
                                  'closingQuotation': v[2], 'highestQuotation': v[3], 'lowestQuotation': v[4],
                                  'volume': v[5], 'amount': v[6], 'vibration': v[7], 'updownPercent': v[8],
                                  'updownAmount': v[9], 'turnoverRate': v[10],'rehabilitation':adjust}
                        i_res = coll.insert_one(record)
                        if i_res is not None:
                            a_total += 1
                        else:
                            f_total += 1
                    else:
                        u_total += 1
                else:
                    i_total += 1
        except Exception as err:
            logging.error(str(err))
            emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='交易历史', content=str(err))
        finally:
            print('交易历史--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            record = {'dataName': '交易历史', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)
            return total, i_total, u_total, a_total, f_total


    #
    def ah_equtity(self):
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        ak.stock_zh_ah_name()


    # 描述: 沪深京 A 股股票代码和股票简称数据
    def ae_quity(self):
        ak.stock_info_a_code_name()

    def get_more_info(self, symbol=None, beg_date=None):
        localcoll = self.mongoHelper.mongo_db.get_collection('equityBasic')
        localcoll2 = self.mongoHelper.mongo_db.get_collection('individualCashFlow')
        join_key = 'code'
        results =[]
        for doc1 in localcoll.find({'code':'600487'}):
            print(doc1)
            doc2 = localcoll2.find_one({join_key: doc1[join_key]})
            if doc2:
                # 如果找到匹配的文档，将它们合并
                results.append({**doc1.copy(), **doc2.copy()})
        # 输出结果
        for result in results:
            print(result)

    def get_all_equities(self) -> []:
        coll = self.mongoHelper.mongo_db.get_collection('equityBasic')
        cursor = coll.find().batch_size(100)
        res = []
        for doc in cursor:
            rec = {'code':doc['code'], 'exchange': doc['exchange']}
            res.append(rec)
        return res

    # 转融通数据
    def lending_securities(self, code=[]):
        total: int = 0
        u_total: int = 0
        i_total: int = 0
        a_total: int = 0
        f_total: int = 0
        try:
            if type(code) == list:
                # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='转融通', content=f'计划更新{len(code)}只股票的转融通数据')

                for item in code:
                    print(item['code'], datetime.now())
                    mycode = item['code']
                    url = 'https://datacenter-web.eastmoney.com/api/data/v1/get'
                    target_code = f'(SECURITY_CODE={mycode})'
                    pageNumber = 1
                    pages = 1
                    params = {
                        'callback': 'jQuery11230786842473102177_1718457245053',
                        'sortColumns': 'TRADE_DATE',
                        'sortTypes': -1,
                        'pageSize': 50,
                        'pageNumber': pageNumber,
                        'reportName': 'RPT_REFINANCE_TRAN_DEL',
                        'columns': 'ALL',
                        'source': 'WEB',
                        'client': 'WEB',
                        'filter': target_code
                    }
                    headers = {
                        'Accept': '*/*',
                        'Accept-Encoding': 'gzip, deflate, br, zstd',
                        'Accept-Language': 'zh-CN,zh;q=0.9',
                        'Connection': 'keep-alive',
                        'Cookie': 'qgqp_b_id=ecacdafacf19708666eb3adbb0f69a4c; st_si=23310874661494; websitepoptg_api_time=1718450229554; st_asi=delete; HAList=ty-105-API-%u58F0%u7F51; st_pvi=76026277595136; st_sp=2023-12-02%2019%3A19%3A57; st_inirUrl=http%3A%2F%2Fquote.eastmoney.com%2Fredirect.html; st_sn=10; st_psi=2024061521140686-113300301000-8596016326; JSESSIONID=644BF22B473860900852C9610B6C4A67',
                        'Host': 'datacenter-web.eastmoney.com',
                        'Referer': 'https://data.eastmoney.com/zrt/zrqHistory/688271.html',
                        'Sec-Ch-Ua': 'Not/A)Brand";v="8", "Chromium";v="126", "Google Chrome";v="126"',
                        'Sec-Ch-Ua-Mobile': '?0',
                        'Sec-Ch-Ua-Platform': "macOS",
                        'Sec-Fetch-Dest': 'script',
                        'Sec-Fetch-Mode': 'no-cors',
                        'Sec-Fetch-Site': 'same-site',
                        'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36'
                    }
                    response = requests.get(url, params=params, headers=headers)
                    # print(response.text)

                    # 检查请求是否成功
                    if response.status_code == 200:
                        # 解析HTML内容
                        match = re.search(r'\(([^()]*)\)', response.text)
                        if match:
                            json_str = match.group(1)
                            try:
                                data = json.loads(json_str)
                                # print(data['result']['data'])
                                coll = self.mongoHelper.mongo_db.get_collection('shareHolderLendingInstitute')
                                if data['code'] == 0:
                                    pages = data['result']['pages']
                                    c = data['result']['count']
                                    for rec in data['result']['data']:
                                        total = total + 1
                                        # print(rec)
                                        record = {'code': rec['SECURITY_CODE'], 'shortName': rec['SECURITY_NAME_ABBR'],
                                                  'tradeDate': rec['TRADE_DATE'],
                                                  'begBalanceNum': rec['BEG_BALANCE_NUM'],
                                                  'endBalanceNum': rec['END_BALANCE_NUM'],
                                                  'marginSecFinNum': rec['MARGINSEC_FIN_NUM'],
                                                  'endBalance': rec['END_BALANCE'],
                                                  'marketCap': rec['MARKET_CAP'], 'closingPrice': rec['CLOSE_PRICE'],
                                                  'changeRate': rec['CHANGE_RATE'], 'exchange': rec['SECUCODE'],
                                                  'innerCode': rec['SECURITY_INNER_CODE'],
                                                  'createdTime': datetime.now(),
                                                  'source': 'EAST'}
                                        q_con = {'code': rec['SECURITY_CODE'], 'tradeDate': rec['TRADE_DATE']}
                                        rec = coll.find_one(q_con)
                                        if rec is None:
                                            i = coll.insert_one(record)
                                            if i.inserted_id is not None:
                                                print(f'{i.inserted_id}successfully')
                                else:
                                    msg = data['message']
                                    print(f'{msg}')
                            except json.JSONDecodeError as e:
                                print(f"JSON解析错误: {e}")
                    else:
                        print("请求失败")
                    if pages > 1:
                        print(pages)
                        for ii in range(2, pages + 1):
                            params['pageNumber'] = ii
                            print(params)
                            response = requests.get(url, params=params, headers=headers)
                            # 检查请求是否成功
                            if response.status_code == 200:
                                # 解析HTML内容
                                match = re.search(r'\(([^()]*)\)', response.text)
                                if match:
                                    json_str = match.group(1)
                                    try:
                                        data = json.loads(json_str)
                                        # print(data['result']['data'])
                                        coll = self.mongoHelper.mongo_db.get_collection('shareHolderLendingInstitute')
                                        for rec in data['result']['data']:
                                            print(rec)
                                            total = total + 1
                                            record = {'code': rec['SECURITY_CODE'],
                                                      'shortName': rec['SECURITY_NAME_ABBR'],
                                                      'tradeDate': rec['TRADE_DATE'],
                                                      'begBalanceNum': rec['BEG_BALANCE_NUM'],
                                                      'endBalanceNum': rec['END_BALANCE_NUM'],
                                                      'marginSecFinNum': rec['MARGINSEC_FIN_NUM'],
                                                      'endBalance': rec['END_BALANCE'],
                                                      'marketCap': rec['MARKET_CAP'],
                                                      'closingPrice': rec['CLOSE_PRICE'],
                                                      'changeRate': rec['CHANGE_RATE'], 'exchange': rec['SECUCODE'],
                                                      'innerCode': rec['SECURITY_INNER_CODE'],
                                                      'createdTime': datetime.now(),
                                                      'source': 'EAST'}
                                            q_con = {'code': rec['SECURITY_CODE'], 'tradeDate': rec['TRADE_DATE']}
                                            rec = coll.find_one(q_con)
                                            if rec is None:
                                                i = coll.insert_one(record)
                                                if i.inserted_id is not None:
                                                    print(f'{i.inserted_id}successfully')

                                    except json.JSONDecodeError as e:
                                        print(f"JSON解析错误: {e}")
                            else:
                                print("请求失败")
        except Exception as e:
            logger.error(str)
        finally:
            print('转融通--', datetime.now().strftime('%Y-%m-%d'), datetime.now())
            # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='转融通',content=f'实际搜索{total}只股票的转融通数据')
            record = {'dataName': '转融通', 'period': 'daily', 'total': total, 'i_total': i_total,
                      'a_total': a_total,
                      'u_total': u_total, 'source': 'AK', 'createdTime': datetime.now(), 'bizDate': None, 'code': None,
                      'exchange': None}
            DataLog.add_log(record)



        def get_daily_trade_info(self, begdate='20240202'):
            ak.stock_lhb_detail_daily_sina(date=begdate)


if __name__ == "__main__":
    a = AkshareWeekly(cfg=None)
    # emailUtils.simplysend(receiver=['liuchuang@9tts.cn'], title='转融通',content=f'实际搜索只股票的转融通数据')
    # a.individualCashFlow('','')
    # a.instituteHolding(symbol='')
    # a.basicInfo(symbol='600487')
    # a.financingSecurities(data="20240520")
    # a.getmoreinfo(symbol='',begdate='')
    list = a.get_all_equities()
    for rec in list:
        a.a_hist(symbol=rec['code'],period='daily',start_date='20240101',end_date='20240620',adjust='hfq')
    # a.instituteHolding(symbol='600487')
    # a.coreBiz(symbol='SH600487')
    # a.pledgeRatio(beg_date='20240530')
    # a.financingSecurities(data='20240603')
    # a.blockTransactionDetails(symbol='A股',begDate='20240530',endDate='20240603')
    # a.bigdealtrack()
    # a.maketfundflow()
    # a.numofretailinvestor(symbol='20231231')
    # a.freeHolder(symbol='sh600487',begdate='20230930')
    # a.equityrepurchase()

    a.lending_securities(code=a.get_all_equities())

    # print(a.get_all_equities())